const query = require('../config/db.config.js')

const projMod = {
  deplistCountMod:async ()=>{
    let sql = 
    ` select
    COUNT(*) as 'sum0',
    sum(sortId = 1) as 'sum1',
    sum(sortId = 2) as 'sum2',
    sum(sortId = 3) as 'sum3'
    from  projinfo  WHERE mark = 0`
    const res = await query(sql)
    // console.log(res);
    return res
  },
  burryPointMod:async (params)=>{
    let { projId , num } = params
    let sql = `UPDATE projinfo SET look=look + ${num} WHERE projId = ${projId}`
    const res = await query(sql)
    return res
  },
  // 收藏状态
  collectStatusMod:async (params)=>{
    let { userId, projId } = params
    let sql =
    `SELECT * FROM collectinfo WHERE projId = ${projId} AND userId = "${userId}"`
    const res = await query(sql)
    return res.length
  },
  // 收藏
  collctProjMod:async (params)=>{
    let { userId, projId } = params
    let sql =
    `INSERT INTO collectinfo
      (projId, userId)
    VALUES
      ( ${projId}, "${userId}")`
    await query(sql)
  },
  // 删除收藏
  deletColletMod:async (params)=>{
    let { userId, projId } = params
    let sql = 
    `DELETE FROM 
      collectinfo 
    WHERE 
    projId = ${projId} AND userId = "${userId}"`
    await query(sql)
  },
  // 我的收藏
  collectListMod:async (userId)=>{
    let sql = 
    `SELECT 
      cl.collectId,
      pr.projName, 
      pr.projId, 
      cl.createTime
    FROM 
      projinfo pr,
      collectinfo cl
    WHERE 
      cl.projId = pr.projId
      And cl.userId = "${userId}"
      ORDER BY createTime DESC
      `
    const res = await query(sql)
    return res
  },
  // 我的发布
  myProjlistMod: async (userId) => {
    let sql = 
    `SELECT 
      pr.projName, 
      pr.projId, 
      pr.createTime,
      pr.mark
    FROM 
      projinfo pr
    WHERE 
      pr.userId = "${userId}"
      ORDER BY createTime DESC`

    const res = await query(sql)
    return res
  },
  // 增加记录
  addprojtMod: async (params) => {
    let { pid, 
          userId, 
          schoolId, 
          sortId, 
          grade,
          department, 
          num, 
          projDesc, 
          projName 
        } = params
    let sql = 
    `INSERT INTO projinfo  
      ( projId, 
        userId, 
        schoolId, 
        sortId, 
        grade,
        department, 
        num, 
        projDesc, 
        projName ) 
    VALUES 
      ( ${pid}, 
        "${userId}",
        ${schoolId}, 
        ${sortId}, 
        "${grade}", 
        "${department || 0 }", 
        ${num}, 
        "${projDesc}", 
        "${projName}" );`
        
    await query(sql)
  },
  // 删除发布记录
  deleteprojMod : async (projId)=>{
    let sql = 
    `DELETE FROM 
      projinfo 
    WHERE 
      projId = ${projId} ;`
    await query(sql)
  },
  // 查询详情
  projDetailMod : async (projId)=>{
    // 项目部分信息
    let sql = 
      `SELECT  
      pr.grade, 
      pr.commNum,
      pr.projName, 
      pr.projDesc, 
      pr.createTime, 
      pr.num,
      dp.depName,
      us.avatarUrl,
      us.nickName,
      us.userId
    FROM 
      projinfo pr,
      depinfo dp,
      userinfo us
    WHERE 
      dp.depId = pr.department
      AND us.userId = pr.userId
      AND pr.projId = ${projId} ;`
    const res = await query(sql)
    return res
  },
  getprojListMod: async (params) => {
   
    if(params.sortId == 0) params.sortId = ''
    let page = (params.page -1)*params.limit
    
    let occ = '' //  如果参数没传（非必传参数）则默认 occ 空串占位
    let sql = 
    `SELECT 
      pr.projId,
      pr.grade, 
      pr.projName, 
      pr.createTime, 
      pr.num,
      pr.look,
      dp.depName,
      us.avatarUrl,
      us.nickName
    FROM projinfo pr , depinfo dp , userinfo us WHERE 
      dp.depId = pr.department AND us.userId = pr.userId
     AND (pr.grade="${ params.grade || occ }" or "${ params.grade || occ }"='') 
     AND (pr.sortId="${ params.sortId || occ }" or "${ params.sortId || occ }"='')
     AND (pr.department="${ params.department || occ }" or "${ params.department || occ }"='')
     AND (pr.schoolId="${ params.schoolId }" or "${ params.schoolId }"='')
     AND (pr.projName LIKE '%${ params.projName || occ }%' )
     AND (pr.num BETWEEN ${ params.num ? params.num[0] : 0 } AND ${ params.num ? params.num[1] : 100})
     AND mark = 0
     LIMIT ${ page },${ params.limit }`

    // let countSql = `SELECT COUNT(*) c FROM projinfo `

    // const count = await query(countSql) //总条数
    const res = await query(sql)    // 所有数据
    // res.total = count[0].c 
    // console.log(res);
    return res
  },
}

module.exports = projMod